# -*- encoding: utf-8 -*-
'''
@File    :   export_and_import_excel.py
@Contact :
@Author  :     ypy

@Modify Time      @Author    @Version    @Desciption
------------      -------    --------    -----------
2021/2/24 16:03   ypy      1.0         导入导出excel文件，只导入导出bet_data和reward_conditions两个model的数据，开奖数据不动
'''
# 库文件
import os
import xlwt
import xlrd
from io import BytesIO
from django.http import HttpResponse

# 本地文件
import SSQ.models as SSQM
import DLT.models as DLTM

# 默认文件写入和上传路径
Save_PATH = 'static/download/'

"""
生成下载excel文件，并返回路径
"""


# 提供excel下载
def export_excel(filename='backup.xls'):
    ws = xlwt.Workbook(encoding="utf-8")
    SSQ_bet_sheet = ws.add_sheet(u'双色球投注数据')
    SSQ_reward_sheet = ws.add_sheet(u'双色球奖励等级')
    DLT_bet_sheet = ws.add_sheet(u'大乐透投注数据')
    DLT_reward_sheet = ws.add_sheet(u'大乐透奖励等级')
    get_SSQ_bet_data(SSQ_bet_sheet)
    get_SSQ_reward_data(SSQ_reward_sheet)
    get_DLT_bet_data(DLT_bet_sheet)
    get_DLT_reward_data(DLT_reward_sheet)

    pathname = save_excel_file_to_local(ws, filename)
    # return download_to_net(ws, filename)
    return pathname


# 获取双色球投注数据
def get_SSQ_bet_data(sheet):
    """
    写入双色球投注信息
    :param sheet: xlwt的sheet类型
    :return:
    """
    bet_data = SSQM.bet_data.objects.all().order_by("-phasenum")
    w = sheet
    if bet_data:
        w.write(0, 0, '期号')
        w.write(0, 1, '红球1')
        w.write(0, 2, '红球2')
        w.write(0, 3, '红球3')
        w.write(0, 4, '红球4')
        w.write(0, 5, '红球5')
        w.write(0, 6, '红球6')
        w.write(0, 7, '篮球')
        w.write(0, 8, '中奖等级')
        w.write(0, 9, '是否已检测')
        w.write(0, 10, '投注倍数')
        w.write(0, 11, '中奖金额')
        w.write(0, 12, '追加投注')
        excel_row = 1
        for bet in bet_data:
            w.write(excel_row, 0, bet.phasenum)
            w.write(excel_row, 1, bet.red1)
            w.write(excel_row, 2, bet.red2)
            w.write(excel_row, 3, bet.red3)
            w.write(excel_row, 4, bet.red4)
            w.write(excel_row, 5, bet.red5)
            w.write(excel_row, 6, bet.red6)
            w.write(excel_row, 7, bet.blue)
            w.write(excel_row, 8, bet.bonuslevel)
            w.write(excel_row, 9, bet.ischeck)
            w.write(excel_row, 10, bet.multiple)
            w.write(excel_row, 11, bet.bonusprice)
            w.write(excel_row, 12, bet.additonal_bet)
            excel_row += 1


# 获取双色球奖励数据
def get_SSQ_reward_data(sheet):
    """

    :param sheet: xlwt的sheet类型
    :return:
    """
    bet_data = SSQM.reward_conditions.objects.all().order_by("-id")
    w = sheet
    if bet_data:
        w.write(0, 0, '奖励等级')
        w.write(0, 1, '投中红球数')
        w.write(0, 2, '投中篮球数')
        w.write(0, 3, '单倍奖金数')

        excel_row = 1
        for bet in bet_data:
            w.write(excel_row, 0, bet.bonuslevel)
            w.write(excel_row, 1, bet.red)
            w.write(excel_row, 2, bet.blue)
            w.write(excel_row, 3, bet.single_bonus)

            excel_row += 1


# 获取大乐透奖励数据
def get_DLT_bet_data(sheet):
    """
    写入大乐透投注信息
    :param sheet: xlwt的sheet类型
    :return:
    """
    bet_data = DLTM.bet_data.objects.all().order_by("-phasenum")
    w = sheet
    if bet_data:
        w.write(0, 0, '期号')
        w.write(0, 1, '红球1')
        w.write(0, 2, '红球2')
        w.write(0, 3, '红球3')
        w.write(0, 4, '红球4')
        w.write(0, 5, '红球5')
        w.write(0, 6, '篮球1')
        w.write(0, 7, '篮球2')
        w.write(0, 8, '中奖等级')
        w.write(0, 9, '是否已检测')
        w.write(0, 10, '投注倍数')
        w.write(0, 11, '中奖金额')
        w.write(0, 12, '追加投注')
        excel_row = 1
        for bet in bet_data:
            w.write(excel_row, 0, bet.phasenum)
            w.write(excel_row, 1, bet.red1)
            w.write(excel_row, 2, bet.red2)
            w.write(excel_row, 3, bet.red3)
            w.write(excel_row, 4, bet.red4)
            w.write(excel_row, 5, bet.red5)
            w.write(excel_row, 6, bet.blue1)
            w.write(excel_row, 7, bet.blue2)
            w.write(excel_row, 8, bet.bonuslevel)
            w.write(excel_row, 9, bet.ischeck)
            w.write(excel_row, 10, bet.multiple)
            w.write(excel_row, 11, bet.bonusprice)
            w.write(excel_row, 12, bet.additonal_bet)
            excel_row += 1


# 获取大乐透奖励数据
def get_DLT_reward_data(sheet):
    """

    :param sheet: xlwt的sheet类型
    :return:
    """
    bet_data = DLTM.reward_conditions.objects.all().order_by("-id")
    w = sheet
    if bet_data:
        w.write(0, 0, '奖励等级')
        w.write(0, 1, '投中红球数')
        w.write(0, 2, '投中篮球数')
        w.write(0, 3, '单倍奖金数')

        excel_row = 1
        for bet in bet_data:
            w.write(excel_row, 0, bet.bonuslevel)
            w.write(excel_row, 1, bet.red)
            w.write(excel_row, 2, bet.blue)
            w.write(excel_row, 3, bet.single_bonus)

            excel_row += 1


# 保存到本地,并返回地址连接数
def save_excel_file_to_local(ws, filename):
    """

    :param ws: workbook
    :param filename: 保存的文件名
    :return:
    """
    path = Save_PATH + filename
    exist_file = os.path.exists(path)
    if exist_file:
        os.remove(path)
    ws.save(path)
    return path


# 提供下载，由于使用ajax提交申请，返回的string字符串，ajax不识别下载文件，该函数暂时不用
def download_to_net(ws, filename):
    sio = BytesIO()
    ws.save(sio)
    sio.seek(0)
    response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=' + filename + '.xls'
    response.write(sio.getvalue())
    return response


"""
导入上传excel数据
"""


# 保存上传文件至本地
def save_upload_excel_to_local(file):
    return


# 读取excel文件，并写入数据库
def import_excel(file):
    ws = xlrd.open_workbook(file)
    SSQ_bet_sheet = ws.sheet_by_name(u'双色球投注数据')
    SSQ_reward_sheet = ws.sheet_by_name(u'双色球奖励等级')
    DLT_bet_sheet = ws.sheet_by_name(u'大乐透投注数据')
    DLT_reward_sheet = ws.sheet_by_name(u'大乐透奖励等级')

    return
